// 数据库迁移模块
use sqlx::SqlitePool;
use tracing::info;

pub async fn run_migrations(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    info!("Running database migrations");
    
    // 创建设备表
    create_devices_table(pool).await?;
    
    // 创建账号表
    create_accounts_table(pool).await?;
    
    // 创建任务表
    create_tasks_table(pool).await?;
    
    // 创建任务日志表
    create_task_logs_table(pool).await?;
    
    // 创建内容表
    create_content_table(pool).await?;
    
    // 创建系统配置表
    create_system_config_table(pool).await?;
    
    info!("Database migrations completed successfully");
    Ok(())
}

async fn create_devices_table(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        CREATE TABLE IF NOT EXISTS devices (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            platform TEXT NOT NULL,
            device_type TEXT NOT NULL,
            model TEXT,
            version TEXT,
            resolution TEXT,
            status TEXT NOT NULL DEFAULT 'offline',
            last_heartbeat TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
        "#
    )
    .execute(pool)
    .await?;
    
    Ok(())
}

async fn create_accounts_table(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        CREATE TABLE IF NOT EXISTS accounts (
            id TEXT PRIMARY KEY,
            username TEXT NOT NULL,
            platform TEXT NOT NULL,
            email TEXT,
            status TEXT NOT NULL DEFAULT 'inactive',
            device_id TEXT,
            profile_data TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            FOREIGN KEY (device_id) REFERENCES devices (id)
        )
        "#
    )
    .execute(pool)
    .await?;
    
    Ok(())
}

async fn create_tasks_table(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        CREATE TABLE IF NOT EXISTS tasks (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            task_type TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'pending',
            priority INTEGER DEFAULT 1,
            config TEXT,
            result TEXT,
            device_id TEXT,
            account_id TEXT,
            progress INTEGER DEFAULT 0,
            current_step TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            FOREIGN KEY (device_id) REFERENCES devices (id),
            FOREIGN KEY (account_id) REFERENCES accounts (id)
        )
        "#
    )
    .execute(pool)
    .await?;
    
    Ok(())
}

async fn create_task_logs_table(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        CREATE TABLE IF NOT EXISTS task_logs (
            id TEXT PRIMARY KEY,
            task_id TEXT NOT NULL,
            level TEXT NOT NULL,
            message TEXT NOT NULL,
            details TEXT,
            step_name TEXT,
            created_at TEXT NOT NULL,
            FOREIGN KEY (task_id) REFERENCES tasks (id)
        )
        "#
    )
    .execute(pool)
    .await?;
    
    Ok(())
}

async fn create_content_table(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        CREATE TABLE IF NOT EXISTS content_items (
            id TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            content_type TEXT NOT NULL,
            platform TEXT NOT NULL,
            file_path TEXT,
            metadata TEXT,
            status TEXT NOT NULL DEFAULT 'draft',
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
        "#
    )
    .execute(pool)
    .await?;
    
    Ok(())
}

async fn create_system_config_table(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        CREATE TABLE IF NOT EXISTS system_config (
            key TEXT PRIMARY KEY,
            value TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
        "#
    )
    .execute(pool)
    .await?;
    
    Ok(())
}